package org.paic.d;

import groovy.sql.GroovyRowResult;
import groovy.sql.Sql;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import org.paic.util.LogUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @author kerry
 */
public class D {
    private static Object lock = new Object();
    // singleton
    private static D inst;

    public static D gen() {
        if (inst != null)
            return inst;

        synchronized (lock) {
            if (inst == null) {
                inst = new D();
                inst.setDialect(new Dialect());
                DataSource ds = Ds.getInstance();
                inst.setDb(new Sql(ds));
            }
        }

        return inst;
    }

    private Logger log = LoggerFactory.getLogger(D.class);

    // 用于分页的sql语句生成
    private Dialect dialect;
    private Sql db;

    public void setDialect(Dialect dialect) {
        this.dialect = dialect;
    }

    public void setDb(Sql db) {
        this.db = db;
    }

    /**
     * 查询记录集
     * 
     * @param sql 要执行的sql
     * @param args PrepareStatement的参数
     * @return 结果集
     * @throws SQLException
     */
    public List<Map<String, Object>> query(String sql, List<Object> args) throws SQLException {
        if (log.isDebugEnabled()) {
            log.debug(sql);
            log.debug(args == null ? "" : args.toString());
        }

        List<GroovyRowResult> ll = args == null ? db.rows(sql) : db.rows(sql, args);
        if (log.isDebugEnabled()) {
            log.debug("Query resultset : " + ll.size());
        }

        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
        for (GroovyRowResult one : ll)
            resultList.add(NamingStyleUtils.transform(one));

        return resultList;
    }

    /**
     * 查询记录集
     * 
     * @param sql 要执行的sql
     * @return 结果集
     * @throws SQLException
     */
    public List<Map<String, Object>> query(String sql) throws SQLException {
        return query(sql, null);
    }

    /**
     * 查询一条记录
     * 
     * @param sql
     * @param args
     * @return 一条记录的键值对集合
     * @throws SQLException
     */
    public Map<String, Object> queryMap(String sql, List<Object> args) throws SQLException {
        List<Map<String, Object>> r = query(sql, args);
        if (r != null && r.size() > 0) {
            return r.get(0);
        }
        return null;
    }

    /**
     * 查询一条记录
     * 
     * @param sql
     * @return 一条记录的键值对集合
     * @throws SQLException
     */
    public Map<String, Object> queryMap(String sql) throws SQLException {
        return queryMap(sql, null);
    }

    /**
     * 分页查询
     * 
     * @param sql
     * @param args
     * @param pageNum 当前页
     * @param pageSize 每页的记录数
     * @return 分页对象
     * @throws SQLException
     */
    public Pager pagi(String sql, List<Object> args, int pageNum, int pageSize) throws SQLException {
        Pager pi = new Pager(pageNum, pageSize);

        List<Map<String, Object>> resultList = query(dialect.getPagiSql(sql, pi.getStart(), pageSize), args);
        pi.setLl(resultList);
        if (resultList != null && resultList.size() > 0) {
            Map<String, Object> countItem = queryMap(dialect.getCountSql(sql), args);

            if (log.isDebugEnabled()) {
                log.debug(countItem == null ? "" : countItem.toString());
            }

            int cc = 0;
            Object totalCount = countItem.get(NamingStyleUtils.toCamel(Dialect.RS_COLUMN, true));
            if (totalCount instanceof BigDecimal)
                cc = ((BigDecimal) totalCount).intValue();
            else
                cc = Integer.parseInt("" + totalCount);

            pi.setTotalCount(cc);
        }
        return pi;
    }

    // 为了拼接sql
    private String genSqlKeys(Set<String> set, String replaced) {
        StringBuilder sb = new StringBuilder();

        int cc = 0;
        for (String it : set) {
            String key = NamingStyleUtils.toUnderline(it);
            if (replaced != null) {
                if (replaced.indexOf("$1") != -1)
                    key = replaced.replace("$1", key);
                else
                    key = replaced;
            }
            sb.append(key);
            if (cc != set.size() - 1)
                sb.append(",");
            cc++;
        }

        return sb.toString();
    }

    /**
     * 插入一条记录
     * 
     * @param ext 对应table各列的键值对，键的明明为小写开始的驼峰式
     * @param table 要插入记录的表名
     * @return 如果有自增id，就返回的新记录的id。其中Oracle sequence是BigDecimal，MySQL是Integer
     * @throws SQLException
     */
    public Object add(Map<String, Object> ext, String table) throws SQLException {
        if (ext == null)
            return null;

        String sql = LogUtils.concatStr("insert into ", table, " (", genSqlKeys(ext.keySet(), null), ") values (",
                genSqlKeys(ext.keySet(), "?"), ")");

        List<Object> args = new ArrayList<Object>(ext.values());

        if (log.isDebugEnabled()) {
            log.debug(sql);
            log.debug(args == null ? "" : args.toString());
        }

        List<List<Object>> ll = db.executeInsert(sql, args);
        if (ll != null && ll.size() > 0)
            return ll.get(0).get(0);

        return null;
    }

    /**
     * 更新一条记录
     * 
     * @param ext 对应table各列的键值对，键的明明为小写开始的驼峰式
     * @param table 要更新记录的表名
     * @param pkCod 主键名，默认为id，ext的键值对中一定要有id=**
     * @return 影响记录数
     * @throws SQLException
     */
    public int update(Map<String, Object> ext, String table, String pkCol) throws SQLException {
        if (ext == null)
            return 0;

        if (pkCol == null)
            pkCol = "id";

        Object pkVal = ext.remove(pkCol);

        String sql = LogUtils.concatStr("update ", table, " set ", genSqlKeys(ext.keySet(), "$1 = ?"), " where ",
                NamingStyleUtils.toUnderline(pkCol), " = ?");

        List<Object> args = new ArrayList<Object>(ext.values());
        args.add(pkVal);

        if (log.isDebugEnabled()) {
            log.debug(sql);
            log.debug(args == null ? "" : args.toString());
        }

        return db.executeUpdate(sql, args);
    }

    /**
     * 执行sql
     * 
     * @param sql 可以是ddl，或者增删改的sql，也可以使存储过程的sql
     * @param args sql参数
     * @return 执行成功与否
     * @throws SQLException
     */
    public boolean exe(String sql, List<Object> args) throws SQLException {
        if (log.isDebugEnabled()) {
            log.debug(sql);
            log.debug(args == null ? "" : args.toString());
        }

        return db.execute(sql, args);
    }

    /**
     * 执行sql
     * 
     * @param sql 可以是ddl，或者增删改的sql，也可以使存储过程的sql
     * @return 执行成功与否
     * @throws SQLException
     */
    public boolean exe(String sql) throws SQLException {
        if (log.isDebugEnabled()) {
            log.debug(sql);
        }

        return db.execute(sql);
    }
}
